﻿CREATE PROCEDURE admin.s_Images_Transfer --WITH EXECUTE AS 'AFC-HOME\SQLServer1'
	@PageTypeID int
AS
SET XACT_ABORT ON
SET NOCOUNT ON
DECLARE @DateAfter datetime SET @DateAfter =GETDATE()-10
--BEGIN TRAN
/*,A.ApplicantID, B.ID,B.FullName,B.SSN,*/
DECLARE curPages CURSOR FAST_FORWARD FOR
SELECT TOP 10000 L_B.LoanID,I.ID ImageID, I.ImageTypeID,P.ID PageID,P.PageNumber-1,PS.Data
FROM t_Borrower B WITH(nolock)
INNER JOIN t_Loan_Borrower L_B WITH(nolock) ON L_B.BorrowerID = B.ID
INNER JOIN t_Loan L WITH(nolock) ON L_B.LoanID = L.ID
INNER JOIN AFCBANK.AFC.dbo.t_Applicants A ON B.SSN = A.SSN
INNER JOIN AFCBANK.Images.dbo.t_Image I ON A.ApplicantID = I.ApplicantID
INNER JOIN AFCBANK.Images.dbo.t_Page P ON I.ID = P.ImageID
INNER JOIN AFCBANK.Images.dbo.t_PageStore PS ON P.ID = PS.PageID
WHERE dbo.SSNCheck(B.SSN ) = 1 AND
			L_B.LoanID > 1000000 AND
			L.StatusID = 94 AND
			P.DateTransfered < @DateAfter AND
			PS.PageTypeID = @PagetypeID

OPEN curPages

DECLARE @LoanID as int,@ImageID as int,@ImageTypeID as int,@PageID as int,@PageNumber as int,@Data as varbinary(max),@Err int,@Count int
FETCH NEXT FROM curPages INTO @LoanID,@ImageID, @ImageTypeID ,@PageID ,@PageNumber , @Data 

WHILE @@FETCH_STATUS = 0 BEGIN --[
SET @Count = ISNULL(@Count,0) + 1
PRINT dbo.sprint4('@LoanID:%s,@ImageTypeID:%s,@PageNumber:%s,@PageTypeID:%s',@LoanID,@ImageTypeID,@PageNumber,@PageTypeID)
EXECUTE AS Login='AFC-HOME\SQLServer'
EXEC s_Page_Insert_Ex @LoanID,@ImageTypeID,@PageNumber,NULL,@PageTypeID,@Data
SET @Err = @@ERROR
REVERT
IF @Err = 0 BEGIN
	EXEC AFCBANK.Images.dbo.s_Page_SetTransferDate @PageID, @ImageID
END

FETCH NEXT FROM curPages INTO @LoanID,@ImageID, @ImageTypeID ,@PageID ,@PageNumber , @Data 
IF dbo.Stop() = 1 BREAK
END --]

CLOSE curPages
DEALLOCATE curPages


--ROLLBACK
--COMMIT
/*
DECLARE @C AS int SET @C = 10000
WHILE @C > 0 BEGIN
ALTER TABLE dbo.t_PageStore	DROP CONSTRAINT PK_t_PageStore
ALTER TABLE dbo.t_PageStore ADD CONSTRAINT	PK_t_PageStore PRIMARY KEY CLUSTERED 
(PageID,PageTypeID) WITH( PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
EXEC admin.s_Images_Transfer
SET @C = @C - 1
IF dbo.Stop() = 1 BREAK
END
*/
